package com.ttg.common.utils.eazypoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.handler.inter.IWriter;
import com.ttg.common.enums.ErrorCodeEnums;
import com.ttg.common.exception.BaseException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.boot.configurationprocessor.json.JSONException;
import org.springframework.boot.configurationprocessor.json.JSONObject;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Decoder;

import javax.imageio.ImageIO;
import javax.servlet .ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * Description：excel操作工具类
 * Author: chenyou
 * Date: 2025/9/29 13:47
 * Company: ttg
 * Copyright: Copyright (c) 2025
 */
public class ExcelUtil {

    /**
     * 表头结构检查打印
     */
    public static void debugEntity(ExcelExportEntity entity, int level) {
        StringBuilder indent = new StringBuilder();
        for (int i = 0; i < level; i++) {
            indent.append("  ");
        }
        System.out.println(indent.toString() + entity.getName() + " -> " + entity.getKey());

        if (entity.getList() != null) {
            for (ExcelExportEntity child : entity.getList()) {
                debugEntity(child, level + 1);
            }
        }
    }

    /**
     * 增强调试 - 检查数据映射
     */
    public static void debugDataMapping(List<Map<String, Object>> dataList) {
        System.out.println("=== 数据映射检查 ===");
        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> data = dataList.get(i);
            System.out.println("第 " + (i + 1) + " 行数据:");
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                System.out.println("  " + entry.getKey() + " = " + entry.getValue());
            }
            System.out.println("---");
        }
    }

    /**
     * 偶数行设置背景色
     */
    private static void setRowBackground(Workbook workbook){
        Sheet sheet = workbook.getSheetAt(0);
        CellStyle styles = ExcelStyleUtil.getStyles(workbook,false,(short) 12);
        for(int i = 0; i <= sheet.getLastRowNum(); i ++) {
            Row row = sheet.getRow(i);
            if (i%2==0){
                for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {
                    Cell cell = row.getCell(j);
                    cell.setCellStyle(styles);
                }
            }
        }
    }

    /**
     * 导出设置隔行背景色
     * @param params 导出参数
     * @param list 数据
     * @param pojoClass pojo类型
     * @param fileName 文件名称
     * @param isSetRowBackground 是否设置隔行背景色
     */
    public static void exportExcel(ExportParams params, List<?> list, Class<?> pojoClass, String fileName,boolean isSetRowBackground, HttpServletResponse response){
        Workbook workbook = ExcelExportUtil.exportExcel(params,pojoClass,list);
        if (workbook != null) {
        }
        if (isSetRowBackground) {
            setRowBackground(workbook);
        }
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setStyle(ExcelStyleUtil.class);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     * @param list           数据
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response){
        ExportParams exportParams = new ExportParams();
        exportParams.setStyle(ExcelStyleUtil.class);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * list map 导出
     * @param list     数据
     * @param fileName 文件名称
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        ExportParams exportParams = new ExportParams();
        exportParams.setStyle(ExcelStyleUtil.class);
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams){
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 默认的 excel 导出
     * @param list     数据
     * @param fileName 文件名称
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook){
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLS.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void downLoadExcel(IWriter<Workbook> workbookIWriter, HttpServletResponse response, String fileName, String fileType) {
        try (ServletOutputStream outputStream = response.getOutputStream(); Workbook workbook = workbookIWriter.get()) {
            if (workbook != null) {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + fileType, "UTF-8"));
                response.setHeader("Access-Control-Expose-Headers", "filename");
                response.setHeader("filename", URLEncoder.encode(fileName + fileType, "UTF-8"));
                workbook.write(outputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new BaseException(ErrorCodeEnums.SYSTEM_ERROR.getErrcode(), "报表导出异常");
        }
    }

    /**
     * excel 导入
     * @param filePath   excel文件路径
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerify 是否检验excel内容
     * @param pojoClass  pojo类型
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerify, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerify  是否检验excel内容
     * @param pojoClass   pojo类型
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("upload/excel/");
        params.setNeedSave(true);
        params.setNeedVerify(needVerify);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }


    /**
     * 上传文件，返回一个workbook
     * @param file
     */
    public static Workbook importExcel(MultipartFile file) throws IOException {
        File toFile = new File(file.getOriginalFilename());
        Workbook workbook = null;
        if(toFile.getPath().endsWith("xls")){
            workbook = new HSSFWorkbook(file.getInputStream());
        }else if(toFile.getPath().endsWith("xlsx")){
            workbook = new XSSFWorkbook(file.getInputStream());
        }else {
            throw new RuntimeException("请确认你上传的文件类型");
        }
        return workbook;
    }

    /**
     * 读取指定sheet的数据
     * @param file 上传的文件
     * @param sheetName 要读取的sheetName
     * @param titleRows 表头行数
     * @param headRows 标题行数
     * @param startRows 表头之前有多少行不要的数据，从1开始，忽略空行
     * @param readRows 要读取多少行数据，从0开始，比如读取十行，值就是9; 不指定时默认为0
     * @param pojoClass 实体
     */
    public static <T> List<T> importExcel(MultipartFile file, String sheetName, Integer titleRows, Integer headRows, Integer startRows, Integer readRows, Class<T> pojoClass) throws Exception {
        Workbook workbook = importExcel(file);
        int numberOfSheets = workbook.getNumberOfSheets();
        List<T> list = null;
        for (int i = 0; i < numberOfSheets; i++) {
            String name = workbook.getSheetName(i).trim();
            if (name.equals(sheetName) || name.endsWith(sheetName)){
                ImportParams params = new ImportParams();
                params.setTitleRows(titleRows);
                params.setHeadRows(headRows);
                params.setStartRows(startRows);
                params.setReadRows(readRows);
                //第几个sheet页
                params.setStartSheetIndex(i);
                final ExcelImportService excelImportService = new ExcelImportService();
                ExcelImportResult<T> result = excelImportService.importExcelByIs(file.getInputStream(), pojoClass, params, false);
                list = result.getList();
                break;
            }
        }
        return list;
    }

    /**
     * 以map的形式导出表格
     * @param list 数据
     */
    public static <T> List<Map<String, Object>> objectToMap(List<T> list){
        List<Map<String, Object>> result = new ArrayList<>();
        Map<String, Object> map = null;
        try {
            for (T item : list) {
                map = new HashMap<>();
                Class<?> clazz = item.getClass();
                for (Field field : clazz.getDeclaredFields()) {
                    field.setAccessible(true);
                    String fieldName = field.getName();
                    Object value = field.get(item);
                    map.put(fieldName, value);
                }
                result.add(map);
            }
            return result;
        }catch (IllegalAccessException e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 设置注解参数
     * @param annotation  注解
     * @param entity
     */
    private static void annotationParams(Excel annotation,ExcelExportEntity entity){
        if (annotation.addressList()){
            entity.setAddressList(annotation.addressList());
            entity.setReplace(annotation.replace());
        }
        entity.setReplace(annotation.replace());
        entity.setOrderNum(Integer.parseInt(annotation.orderNum()));
        entity.setGroupName(annotation.groupName());
        entity.setNeedMerge(annotation.needMerge());
        entity.setMergeVertical(annotation.mergeVertical());
    }

    /**
     * 动态导出列，根据Excel注解获取列的字段注释（表头名）、宽度
     * @param clazz
     * @param fields 选择要导出的列
     * @param changeHead 要更改表头的列，格式是{"字段1":"更改的表头1","字段2":"更改的表头2"}
     */
    public static List<ExcelExportEntity> dynamicExport(Class<?> clazz,String fields, JSONObject changeHead) {
        List<ExcelExportEntity> beanList = new ArrayList<>();
        String[] split = fields.split(",");
        int length = split.length;
        try {
            for (int i = 0; i < length; i++) {
                Field f = clazz.getDeclaredField(split[i]);
                Excel annotation = f.getAnnotation((Excel.class));
                String comment = null;
                if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){
                    comment = changeHead.get(f.getName()).toString();
                }
                Double width = annotation.width();
                ExcelExportEntity entity = new ExcelExportEntity(comment, f.getName(), width.intValue());
                annotationParams(annotation,entity);
                beanList.add(entity);
            }
        }catch (NoSuchFieldException | JSONException e){
            e.printStackTrace();
        }
        return beanList;
    }

    /**
     * 动态导出列（选择要忽略的列），根据Excel注解获取列的字段注释（表头名）、宽度
     * @param clazz
     * @param fields 选择要忽略的列
     * @param changeHead 要更改表头的列，格式是{"字段名1":"更改的表头1","字段名2":"更改的表头2"}
     */
    public static List<ExcelExportEntity> dynamicIgnoreExport(Class<?> clazz, String fields, JSONObject changeHead) throws JSONException {
        List<ExcelExportEntity> beanList = new ArrayList<>();
        String[] split = fields.split(",");
        int length = split.length;
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field f : declaredFields) {
            Excel annotation = f.getAnnotation((Excel.class));
            if (annotation != null){
                boolean flag = false;
                for (int i = 0; i < length; i++) {
                    if (f.getName().equals(split[i])){
                        flag = true;
                        break;
                    }
                }
                if (flag) {
                    continue;
                }
                String comment = null;
                if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){
                    comment = changeHead.get(f.getName()).toString();
                }
                Double width = annotation.width();
                ExcelExportEntity entity = new ExcelExportEntity(comment, f.getName(), width.intValue());
                annotationParams(annotation,entity);
                beanList.add(entity);
            }
        }
        return beanList;
    }

    /**
     * 导出Excel，并在最后追加图片
     * @param sheetName sheet名称
     * @param wb        HSSFWorkbook对象
     */
    public static Workbook getWorkbook(String sheetName, Workbook wb, String imgUrl) throws IOException {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.getSheet(sheetName);
        /*生成图表*/
        if(!StringUtils.isEmpty(imgUrl)) {
            String[] imgUrlArr = imgUrl.split("base64,");//拆分base64编码后部分
            byte[] buffer = new BASE64Decoder().decodeBuffer(imgUrlArr[1]);
            String picPath = System.getProperty("user.dir")+"\\upload\\image\\pic.png";
            File file = new File(picPath);//图片文件
            try {
                //生成图片
                OutputStream out = new FileOutputStream(file);//图片输出流
                out.write(buffer);
                out.flush();//清空流
                out.close();//关闭流
                ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
                BufferedImage bufferImg = ImageIO.read(new File(picPath));
                ImageIO.write(bufferImg, "PNG", outStream);
                // 利用HSSFPatriarch将图片写入EXCEL
                Drawing<?> drawing = sheet.createDrawingPatriarch();
                //位置：第1个单元格中x轴的偏移量、第1个单元格中y轴的偏移量、 第2个单元格中x轴的偏移量、 第2个单元格中y轴的偏移量、第1个单元格的列号、第1个单元格的行号、 第2个单元格的列号、第2个单元格的行号
                //HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 8, (short) 10, 40);
                ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 6, 9, 40);
                drawing.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            if (file.exists()) {
                file.delete();//删除图片
            }
        }
        return wb;
    }
}
